# -*- coding: utf-8 -*-
"""
@Time ： 2023/3/15 10:12
@Author ： ChuiZi
@File ：db_pack.py
@IDE ：PyCharm
"""
import logging

import pymysql
import pymssql
import cx_Oracle as cx


class DB:
    # 和游标的初始化
    def __init__(self, is_commit=True):
        self.__connect = None
        self.__cursor = None
        self.is_commit = is_commit  # 数据库操作无需额外确认

    def __del__(self):
        # 自动关闭
        self.__cursor.close()
        self.__connect.close()

    # 不做任何限制的sql查询方法
    def find(self, sql: str):
        """
        注:只能进行查询操作

        :param sql: 数据库sql查询语法
        :return:
        """
        try:
            self.__cursor.execute(str(sql).strip())
            if self.is_commit:
                self.__connect.commit()
            return self.__cursor.fetchall()
        except Exception as err:
            # 发生错误时回滚
            self.__connect.rollback()
            logging.error(("error %s" % err))

    def execute(self, sql: str):
        """
        可进行所有sql语句,但没有返回值

        :param sql: 数据库sql语句
        :return: None
        """
        try:
            self.__cursor.execute(str(sql).strip())
            if self.is_commit:
                self.__connect.commit()
        except Exception as err:
            # 发生错误时回滚
            self.__connect.rollback()
            logging.error(("error %s" % err))

    # 手动确认数据库操作
    def commited(self):
        self.__connect.commit()

    # region 数据库调用方法
    def mysql(self, host: str, user: str, password: str, database: str = None, port: int = 3306, charset="utf8",
              connect_timeout: int = 10):
        """
        :param host: 服务器地址
        :param user: 用户名
        :param password: 密码
        :param port: 端口号
        :param database: 数据库名称
        :param charset: 字符格式
        :param connect_timeout: 连接超时时间
        :return:
        """
        if self.__connect is not None:
            self.__cursor.close()
            self.__connect.close()
        self.__connect = pymysql.connect(host=host, user=user, password=password, database=database, port=port,
                                         charset=charset, connect_timeout=connect_timeout)
        self.__cursor = self.__connect.__cursor()

    def oracle(self, host: str, user: str, password: str, port: int = 1521, service: str = "orcl"):
        """
        :param host: 服务器地址
        :param user: 用户名
        :param password: 密码
        :param port: 端口号
        :param service: 服务名
        :return:
        """
        if self.__connect is not None:
            self.__cursor.close()
            self.__connect.close()
        self.__connect = cx.connect(user, password, f'{host}:{int(port)}/{service}')
        self.__cursor = self.__connect.__cursor()

    def qlServer(self, server: str, user: str, password: str, database_name: str):
        """
        :param server: 服务器
        :param user: 用户名
        :param password: 密码
        :param database_name: 数据库名
        :return:
        """
        if self.__connect is not None:
            self.__cursor.close()
            self.__connect.close()
        self.__connect = pymssql.connect(server=server, user=user, password=password, database=database_name)
        self.__cursor = self.__connect.__cursor()
    # endregion


db = DB()

if __name__ == '__main__':
    # db.mysql("服务器地址", "用户名", "密码")
    # db.find("select * from tablename")
    pass
